package com.itheima.jdbc;

import com.itheima.pojo.Account;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class JdbcDemo_ResultSet {
    /**
     *
     * @throws Exception
     */
    @Test
    public void testRS() throws Exception {

        //注册驱动  5.5版本后可以省略
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        String url = "jdbc:mysql://47.100.230.149:3306/myheima";
        String username = "myheima";
        String password = "myheima";

        Connection conn = DriverManager.getConnection(url, username, password);

        //定义sql
        String sql = "SELECT * FROM account";

        //获取sql执行对象
        Statement statement = conn.createStatement();

        //执行sql
        ResultSet rs = statement.executeQuery(sql);

        //处理结果
        while (rs.next()){
            System.out.print(rs.getInt("id") + "\t");
            System.out.print(rs.getString("name") + "\t");
            System.out.print(rs.getDouble("money"));
            System.out.println();
        }

        //释放资源
        rs.close();
        statement.close();
        conn.close();
    }

    /**
     * 将结果打包到集合中
     * @throws Exception
     */
    @Test
    public void testRS2() throws Exception {

        //注册驱动  5.5版本后可以省略
        Class.forName("com.mysql.jdbc.Driver");

        //获取连接
        String url = "jdbc:mysql://47.100.230.149:3306/myheima";
        String username = "myheima";
        String password = "myheima";

        Connection conn = DriverManager.getConnection(url, username, password);

        //定义sql
        String sql = "SELECT * FROM account";

        //获取sql执行对象
        Statement statement = conn.createStatement();
        //执行sql
        ResultSet rs = statement.executeQuery(sql);

        //处理结果
        //创建集合
        List<Account> accountList = new ArrayList<>();
        while (rs.next()){
            Account account = new Account();
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            account.setId(id);
            account.setName(name);
            account.setMoney(money);

            accountList.add(account);
        }

        System.out.println(accountList);
        //释放资源
        rs.close();
        statement.close();
        conn.close();
    }
}
